RedshiftでLAGウィンドウ関数を使ってみる

RedshiftでLAGウィンドウ関数を使ってみる

Clock Icon2023.02.06

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、データアナリティクス事業本部@那覇オフィスの下地です。

最近、RedshiftにてLAG ウィンドウ関数を使う機会がありました。公式サイトよりLAG ウィンドウ関数の概要を以下に引用致します。

LAG ウィンドウ関数は、パーティションの現在の行より上 (前) の指定されたオフセットの行の値を返します。

例えば製品の売上を管理するテーブルがあった場合、同一製品の前日売上数などのカラムを同一レコードとして表示して比較するなどが出来ます。

今回は、オプションの指定を含めて試してみようと思います。

1. 前準備

LAG関数を利用するにあたり、以下のような売上の一時テーブルを作成します。

create temp table tmp_sales_table
as
select '1' as id, 'サーターアンダギー' as name, '2023-01-10' as date, 10 as sales_counts
union all
select '2' as id, 'サーターアンダギー' as name, '2023-01-11' as date, 20 as sales_counts
union all
select '3' as id, 'サーターアンダギー' as name, '2023-01-12' as date, null as sales_counts
union all
select '4' as id, 'サーターアンダギー' as name, '2023-01-13' as date, 40 as sales_counts
union all
select '5' as id, '砂糖天ぷら' as name, '2023-01-10' as date, 2 as sales_counts
union all
select '6' as id, '砂糖天ぷら' as name, '2023-01-12' as date, 5 as sales_counts
union all
select '7' as id, '砂糖天ぷら' as name, '2023-01-13' as date, 10 as sales_counts
;

2. 実装

売上の一時テーブルを利用して、各商品に対して前日/2日前の売上データを取得してみたいと思います。

2.1 構文

LAG ウィンドウ関数構文は以下となります。

LAG (value_expr [, offset ])
[ IGNORE NULLS | RESPECT NULLS ]
OVER ( [ PARTITION BY window_partition ] ORDER BY window_ordering )

引数の内容について表にまとめます。

引数 内容 備考
value_expr 関数の対象となる列または式
offset 値を返す現在の行より前の行数を指定するオプションのパラメータ デフォルト設定は1
IGNORE NULLS Amazon Redshift が使用する行を決定するときに null 値をスキップすることを指定するオプション
RESPECT NULLS Amazon Redshift は使用される行を決定するために null 値を含める デフォルト設定
OVER ウィンドウのパーティションおよび並び順を指定
PARTITION BY OVER 句の各グループのレコードの範囲を設定するオプションの引数
ORDER BY 各パーティション内の行をソート

2.2 実行コードと指定オプション

今回は3つのパターンを取得してみたいと思います。

  1. 出力対象カラムにNull値を含む1行前のレコードの値を取得
  2. 出力対象カラムにNull値を含む2行前のレコードの値を取得
  3. 出力対象カラムにNull値を含まず1行前のレコードの値を取得

実行する際の引数を表にまとめます。Null値を含む場合は「RESPECT NULLS」、含まない場合は「IGNORE NULLS」になりますのでどちらか片方を選択します。

実行引数/パターン value_expr offset RESPECT NULLS IGNORE NULLS PARTITION BY ORDER BY
パターン1 sales_counts 1 name date
パターン2 sales_counts 2 name date
パターン3 sales_counts 1 name date

上記表を元にしたクエリになります。

select *
, LAG(sales_counts, 1) RESPECT NULLS OVER (PARTITION BY name ORDER BY date) as pattern_1
, LAG(sales_counts, 2) RESPECT NULLS OVER (PARTITION BY name ORDER BY date) as pattern_2
, LAG(sales_counts, 1) IGNORE  NULLS OVER (PARTITION BY name ORDER BY date) as pattern_3
from tmp_sales_table
order by name, date;

2.3 実行結果

コードを実行した結果の図は以下となります。 3パターンそれぞれで意図した結果が取得できたことが確認できました!

2.4 おまけ

ところで、「砂糖天ぷら」のことを沖縄方言では「サーターアンダギー」と言います。

今回作成した一時テーブルには両方が混在しておりますのでcase文を使用して名前を統一するようにコードを修正し再度実行します。

select case name when '砂糖天ぷら' then 'サーターアンダギー' else name end as rename 
, date
, SUM(sales_counts) as total_counts
, LAG(total_counts, 1) RESPECT NULLS OVER (PARTITION BY rename ORDER BY date) as pattern_1
, LAG(total_counts, 2) RESPECT NULLS OVER (PARTITION BY rename ORDER BY date) as pattern_2
, LAG(total_counts, 1) IGNORE  NULLS OVER (PARTITION BY rename ORDER BY date) as pattern_3
from tmp_sales_table
group by rename, date
order by rename, date;

先ほどは、サーターアンダギーの「2023-01-12」項目がNullになっておりましたが集計されてことにより数値が入って処理されました。

3. まとめ

LAGウィンドウ関数の実行方法についてまとめました。

オプション設定を含めて使えると、とても便利だなと実感しましたので他にサポートされているウィンドウ関数も試していきたいなと思います。

4. 参考リンク

Share this article

facebook logohatena logotwitter logo

© Classmethod, Inc. All rights reserved.